import datetime

from other.cfg import Config
from other.change_format import change_format
from other.status import Status
from server.Mysql import MySQL
from other.sql_montage import sqlMontage


class dicts:
    def __init__(self):
        self.cfg = Config()
        self.st = Status()
        self.db = MySQL(self.cfg.res['mysql']['database'], 'sys_dict_type')
        self.cf = change_format()
        self.sm = sqlMontage()

    # 获取字典
    def get_dict_type(self, data):
        result = {'msg': '成功', 'code': 200, 'data': [], 'total': None}
        sqls = "where 1=1 "
        sql = ""
        pages = ''
        if data != {}:
            if data.get('pageNum') is not None and data.get('pageSize') is not None:
                if data['pageNum'] != '' and data['pageSize'] != '':
                    pages = " limit {},{}".format(int(data['pageNum']) * int(data['pageSize']) - int(data['pageSize']),
                                                  int(data['pageSize']))
            if data.get('dictName') is not None:
                if data['dictName'] != '':
                    sql = sql + " and `dict_name` like '%{}%'".format(data['dictName'])
            if data.get('dictType') is not None:
                if data['dictType'] != '':
                    sql = sql + " and `dict_type`='{}'".format(data['dictType'])
            if data.get('status') is not None:
                if data['status'] != '':
                    sql = sql + " and `status`='{}'".format(data['status'])
            if data.get('beginTime') is not None and data.get('endTime') is not None:
                if data['beginTime'] != '' and data['endTime'] != '':
                    sql = (sql + " and ('{}'<=`create_time` and `create_time`<='{}')".
                           format(data['beginTime'], data['endTime']))
        total = self.db.get(sqls + sql)
        result['total'] = len(total)
        if sql != "":
            sql = sqls + sql + f'{pages}'
        else:
            sql = sqls + f'{pages}'
        res = self.db.get(sql)
        if res != ():
            for r in res:
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 新增字典类型
    def dict_type_add(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"where (`dict_name`='{data['dictName']}' or `dict_type`='{data['dictType']}')"
        g_res = self.db.get(g_sql)
        if g_res != ():
            result['code'] = 500
            for g in g_res:
                if g['dict_name'] == data['dictName']:
                    result['msg'] = '字典名称重复'
                if g['dict_type'] == data['dictType']:
                    result['msg'] = '字典类型重复'
        else:
            data['create_by'] = userInfo['userName']
            data['create_time'] = times
            add_sql = self.sm.add_sql_data(data, 'sys_dict_type')
            # print('add_sql', add_sql)
            self.db.adds(add_sql, 'sys_dict_type')
        return result

    # 修改字典类型
    def dict_type_edit(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"where (`dict_name`='{data['dictName']}' or `dict_type`='{data['dictType']}')"
        g_res = self.db.get(g_sql)
        ff = False
        if g_res != ():
            for g in g_res:
                if g['dict_id'] == data['dictId']:
                    ff = True
                else:
                    if g['dict_name'] == data['dictName']:
                        result['msg'] = '字典名称重复'
                    if g['dict_type'] == data['dictType']:
                        result['msg'] = '字典类型重复'
                    ff = False
                    result['code'] = 500
                    break
        else:
            ff = True
        if ff:
            # e_sql = f"`dict_name`='{data['dictName']}',`dict_type`='{data['dictType']}',`status`='{data['status']}',`update_by`='{userInfo['userName']}',`update_time`='{times}',`remark`='{data['remark']}' where `dict_id`={data['dictId']}"
            # self.db.edit(e_sql)
            if 'createTime' in data:
                del data['createTime']
            data['update_by'] = userInfo['userName']
            data['update_time'] = times
            edit_sql = self.sm.edit_sql_data(data, 'sys_dict_type', 'dict_id', data['dictId'])
            self.db.edits(edit_sql, 'sys_dict_type')
        return result

    # 删除字典类型
    def dict_type_del(self, data):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        ids = [int(x) for x in data.split(',')]
        for i in ids:
            g_sql = f"where `dict_id`={i}"
            g_res = self.db.get(g_sql)
            if g_res != ():
                for g in g_res:
                    dtd_sql = f"delete from `sys_dict_data` where `dict_type`='{g['dict_type']}'"
                    self.db.edits(dtd_sql, 'sys_dict_data')
            self.db.delete(g_sql)
        return result

    # 获取字典
    def get_dict_data(self, data):
        result = {'msg': '成功', 'code': 200, 'data': [], 'total': None}
        sqls = "select * from `sys_dict_data` where 1=1 "
        sql = ""
        pages = ''
        if data != {}:
            if data.get('pageNum') is not None and data.get('pageSize') is not None:
                if data['pageNum'] != '' and data['pageSize'] != '':
                    pages = " limit {},{}".format(int(data['pageNum']) * int(data['pageSize']) - int(data['pageSize']),
                                                  int(data['pageSize']))
            if data.get('dictType') is not None:
                if data['dictType'] != '':
                    sql = sql + " and `dict_type`='{}'".format(data['dictType'])
            if data.get('dictLabel') is not None:
                if data['dictLabel'] != '':
                    sql = sql + " and `dict_label`='{}'".format(data['dictLabel'])
            if data.get('status') is not None:
                if data['status'] != '':
                    sql = sql + " and `status`='{}'".format(data['status'])
            if data.get('beginTime') is not None and data.get('endTime') is not None:
                if data['beginTime'] != '' and data['endTime'] != '':
                    sql = (sql + " and ('{}'<=`create_time` and `create_time`<='{}')".
                           format(data['beginTime'], data['endTime']))
        # print('total', sqls + sql)
        total = self.db.gets(sqls + sql)
        result['total'] = len(total)
        if sql != "":
            sql = sqls + sql + f'{pages}'
        else:
            sql = sqls + f'{pages}'
        # print(sql)
        res = self.db.gets(sql)
        if res != ():
            for r in res:
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 获取字典数据
    def get_dict_type_data(self, data):
        result = {'msg': '成功', 'code': 200, 'data': [], 'total': None}
        sql = f"select * from `sys_dict_data` where `dict_type`='{data}'"
        res = self.db.gets(sql)
        if res != ():
            for r in res:
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 新增字典数据
    def dict_data_add(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"select * from `sys_dict_data` where `dict_type`='{data['dictType']}'"
        g_res = self.db.gets(g_sql)
        ff = True
        if g_res != ():
            for g in g_res:
                if g['dict_label'] == data['dictLabel']:
                    result['code'] = 500
                    result['msg'] = '字典标签重复'
                    return result
                if g['dict_value'] == data['dictValue']:
                    result['code'] = 500
                    result['msg'] = '字典键值重复'
                    return result
        if ff:
            data['create_by'] = userInfo['userName']
            data['create_time'] = times
            add_sql = self.sm.add_sql_data(data, 'sys_dict_data')
            # print('add_sql', add_sql)
            self.db.adds(add_sql, 'sys_dict_data')
        return result

    # 修改字典数据
    def dict_data_edit(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        # print('dict_data_edit', data)
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"select * from `sys_dict_data` where `dict_type`='{data['dictType']}'"
        g_res = self.db.gets(g_sql)
        ff = False
        if g_res != ():
            for g in g_res:
                if g['dict_label'] == data['dictLabel']:
                    if g['dict_code'] == data['dictCode']:
                        ff = True
                if g['dict_value'] == data['dictValue']:
                    if g['dict_code'] == data['dictCode']:
                        ff = True
        else:
            ff = True
        if ff:
            if 'createTime' in data:
                del data['createTime']
            data['update_by'] = userInfo['userName']
            data['update_time'] = times
            edit_sql = self.sm.edit_sql_data(data, 'sys_dict_data', 'dict_code', data['dictCode'])
            # print('edit_sql', edit_sql)
            self.db.edits(edit_sql, 'sys_dict_data')
        return result

    # 删除字典数据
    def dict_data_del(self, data):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        ids = [int(x) for x in data.split(',')]
        for i in ids:
            # 删除
            d_sql = f"delete from `sys_dict_data` where `dict_code`={i}"
            self.db.edits(d_sql, 'sys_dict_data')
        return result
